﻿package com.subway.babynote;

/*水*/
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * Provides access to a database of notes. Each note has a title, the note
 * itself, a creation date and a modified data.
 */
public class NotedbHelper {

	private static final String TAG = "Baby_NotedbHelper";
	public static final String DATABASE_NAME = "babynote.db";
	private static final String VISITORS_TABLE_CREATE = "create table visitors ("
			+ " _id INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ "visitor_id INT UNIQUE NOT NULL,"
			+ "imsi TEXT,"
			+ "imei TEXT,"
			+ "phone_number TEXT,"
			+ "first_use_time INTEGER,"
			+ "birth INTEGER," + "gender INTEGER" + ");";
	private static final String USERS_TABLE_CREATE = "CREATE TABLE users ("
			+ "_id INTEGER  PRIMARY KEY AUTOINCREMENT ,"
			+ " user_id INT UNIQUE NOT NULL," + " imsi TEXT," + " imei TEXT,"
			+ " phone_number TEXT," + " first_use_time DATETIME,"
			+ " birth INTEGER," + "gender INTEGER,"
			+ " email TEXT UNIQUE NOT NULL," + "passwd TEXT,"
			+ "first_reg_time INTEGER," + "ts INTEGER" + ");";
	private static final String WEIBO_TABLE_CREATE = "CREATE TABLE weibo ("
			+ "user_id INTEGER NOT NULL," + "weibo_account TEXT,"
			+ "credential TEXT," + "ts INTEGER" + ");";
	private static final String FOODS_TABLE_CREATE = "CREATE TABLE foods ("
			+ "_id INTEGER  PRIMARY KEY  AUTOINCREMENT," + "food_id INTEGER ,"
			+ "food_prop_id INTEGER NOT NULL," + "foodname TEXT,"
			+ "unit TEXT," + "category TEXT," + "ts INTEGER " + ");";
	private static final String FOODPROP_TABLE_CREATE = "CREATE TABLE food_prop ("
			+ "food_prop_id INTEGER PRIMARY KEY,"
			+ "food_prop TEXT NOT NULL,"
			+ "ifcount INTEGER," + "ts INTEGER" + ");";
	private static final String FEEDS_TABLE_CREATE = "CREATE TABLE feeds ("
			+ "_id INTEGER primary key autoincrement,"
			+ "food_id INTEGER NOT NULL," + "user_id INTEGER NOT NULL,"
			+ "r_date INTEGER NOT NULL," + "r_time INTEGER NOT NULL,"
			+ "quantity REAL NOT NULL," + "ts INTEGER NOT NULL" + ");";

	private static final String TIPS_TABLE_CREATE = "CREATE TABLE tips ("
			+ "_id INTEGER primary key autoincrement," + "s_date INTEGER,"
			+ "s_id INTEGER," + "content TEXT NOT NULL,"
			+ "unique_id INTEGER NOT NULL);";
	private static final String OURCONTACT_TABLE_CREATE = "create table ourcontact ("
			+ "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ "name TEXT,"
			+ " value TEXT," + "ts INTEGER" + ");";
	private static final String LOADINFO_TABLE_CREATE = "create table loadinfo ("
			+ "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ "content TEXT,"
			+ "ts INTEGER" + ");";

	private static final String USAGES_TABLE_CREATE = "CREATE TABLE usages ("
			+ "user_id INTEGER UNIQUE NOT NULL,"
			+ "last_access_time  TEXT NOT NULL,"
			+ "first_access_time TEXT NOT NULL" + ");";
	private static final String HW_TABLE_CREATE = " create table hw ("
			+ "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ "age INTEGER NOT NULL," + "gender INTEGER NOT NULL,"
			+ "min_w REAL," + " mid_w REAL," + "max_w REAL," + "min_h REAL,"
			+ "mid_h REAL," + "max_h REAL," + "ts INTEGER" + ");";
	private static final String LOCAL_AVG_TABLE_CREATE = "create table local_avg ("
			+ "_id INTEGER PRIMARY KEY AUTOINCREMENT ,"
			+ "food_prop_id INTEGER NOT NULL,"
			+ "r_date INTEGER NOT NULL,"
			+ "food_prop TEXT NOT NULL,"
			+ "yours REAL,"
			+ "avgs REAL,"
			+ "diffs REAL" + ");";
	private static final String FEEDBACK_TABLE_CREATE = "create table feedbacks ("
			+ "_id INTEGER PRIMARY KEY  AUTOINCREMENT ,"
			+ "user_id INTEGER NOT NULL,"
			+ "r_time INTEGER,"
			+ "content TEXT NOT NULL" + " );";
	private static final String HWUSER_TABLE_CREATE = "create table hwuser ("
			+ "_id INTEGER PRIMARY KEY  AUTOINCREMENT ,"
			+ "user_id INTEGER NOT NULL," + "height REAL," + "weight REAL,"
			+ "ts INTEGER" + " );";
	SQLiteDatabase db;
	Context context;

	NotedbHelper(Context _context) {
		context = _context;
		db = context.openOrCreateDatabase(DATABASE_NAME, 0, null);

		Log.v(TAG, "db path=" + db.getPath());
	}

	public void CreateTable_visitors() {
		try {
			db.execSQL(VISITORS_TABLE_CREATE);
			Log.v("babynote", "Create Table visitors ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table visitors err,table exists.");
		}
	}

	public void CreateTable_users() {
		try {
			db.execSQL(USERS_TABLE_CREATE);
			Log.v("babynote", "Create Tables users ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table users err,table exists.");
		}
	}

	public void CreateTable_weibo() {
		try {
			db.execSQL(WEIBO_TABLE_CREATE);
			Log.v("babynote", "Create Tables weibo ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table weibo err,table exists.");
		}
	}

	public void CreateTable_foods() {
		try {
			db.execSQL(FOODS_TABLE_CREATE);
			Log.v("babynote", "Create Table foods ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table foods err,table exists.");
		}
	}

	public void CreateTable_foodprop() {
		try {
			db.execSQL(FOODPROP_TABLE_CREATE);
			Log.v("babynote", "Create Table foodprop ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table foodprop err,table exists.");
		}
	}

	public void CreateTable_hw() {
		try {
			db.execSQL(HW_TABLE_CREATE);
			Log.v("babynote", "Create Table HW ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table HW err,table exists.");
		}
	}

	public void CreateTable_hwuser() {
		try {
			db.execSQL(HWUSER_TABLE_CREATE);
			Log.v("babynote", "Create Table HWsuerok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table HWuser err,table exists.");
		}
	}

	public void CreateTable_feedback() {
		try {
			db.execSQL(FEEDBACK_TABLE_CREATE);
			Log.v("babynote", "Create Table feedback ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table feedback err,table exists.");
		}
	}

	public void CreateTable_feeds() {
		try {
			db.execSQL(FEEDS_TABLE_CREATE);
			Log.v("babynote", "Create Table feeds ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table feeds err,table exists.");
		}
	}

	public void CreateTable_ourcontact() {
		try {
			db.execSQL(OURCONTACT_TABLE_CREATE);
			Log.v("babynote", "Create Table feeds ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table feeds err,table exists.");
		}
	}

	public void CreateTable_loadinfo() {
		try {
			db.execSQL(LOADINFO_TABLE_CREATE);
			Log.v("babynote", "Create Table loadinfo ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table loadinfo err,table exists.");
		}
	}

	public void CreateTable_localavg() {
		try {
			db.execSQL(LOCAL_AVG_TABLE_CREATE);
			Log.v("babynote", "Create Table feeds ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table feeds err,table exists.");
		}
	}

	public void CreateTable_tips() {
		try {
			db.execSQL(TIPS_TABLE_CREATE);
			Log.v("babynote", "Create Tables tips ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table tips err,table exists.");
		}
	}

	public void CreateTable_usages() {
		try {
			db.execSQL(USAGES_TABLE_CREATE);
			Log.v("babynote", "Create Tables usages ok");
		} catch (Exception e) {
			Log.v("babynote", "Create Table usages err,table exists.");
		}
	}

	public void InitFoodProp() {
		String sql = "insert into food_prop(food_prop_id, food_prop, ifcount, ts)	values ";
		try {
			db.execSQL(sql + "(1,'母乳',1,1293811200)");
			db.execSQL(sql + "(2,'奶粉',1,1293811200)");
			db.execSQL(sql + "(3,'水果蔬菜泥',1,1293811200)");
			db.execSQL(sql + "(4,'米粉奶糊面粥泥',1,1293811200)");
			db.execSQL(sql + "(5,'蛋黄泥',1,1293811200)");
			db.execSQL(sql + "(6,'豆腐泥',1,1293811200)");
			db.execSQL(sql + "(7,'藕粉',1,1293811200)");
			db.execSQL(sql + "(8,'肉泥肝泥',1,1293811200)");
			db.execSQL(sql + "(9,'钙',0,1293811200)");
			db.execSQL(sql + "(10,'铁',0,1293811200)");
			db.execSQL(sql + "(11,'锌',0,1293811200)");
			db.execSQL(sql + "(12,'维生素',0,1293811200)");
			db.execSQL(sql + "(13,'DHA',0,1293811200)");
			db.execSQL(sql + "(14,'牛初乳益生菌',0,1293811200)");
			db.execSQL(sql + "(15,'蛋白粉',0,1293811200)");
			db.execSQL(sql + "(16,'鱼肝油',0,1293811200)");
			db.execSQL(sql + "(17,'水',1,1293811200)");
			Log.v("babynote", "insert into food_prop ok");
		} catch (Exception e) {
			Log.v("babynote", "init food_prop e=" + e.getMessage());
		}
	}

	public void InitHW() {
		String sql = "insert into hw(age,gender,min_w,mid_w,max_w,min_h,mid_h,max_h, ts) values";
		try {
			db.execSQL(sql
					+ "(0,1, 2.4 , 3.3 , 4.3 , 45.9 , 50.5 , 55.1 ,1293811200)");
			Log.v("babynote", "insert into hw 0,1 ok");
			db.execSQL(sql
					+ "(1,1, 2.9 , 4.3 , 5.6 , 49.7 , 54.6 , 59.5 ,1293811200)");
			Log.v("babynote", "insert into hw 1,1 ok");
			db.execSQL(sql
					+ "(2,1, 3.5 , 5.2 , 6.8 , 52.9 , 58.1 , 63.2 ,1293811200)");
			Log.v("babynote", "insert into hw 2,1 ok");
			db.execSQL(sql
					+ "(3,1, 4.1 , 6.0 , 7.7 , 55.8 , 61.1 , 66.4 ,1293811200)");
			Log.v("babynote", "insert into hw 3,1 ok");
			db.execSQL(sql
					+ "(4,1,4.7,6.7,8.5,58.3,63.7,69.1,1293811200)");
			db.execSQL(sql
					+ "(5,1, 5.3 , 7.3 , 9.2 , 60.5 , 65.9 , 71.3 ,1293811200)");
			Log.v("babynote", "insert into hw 5,1 ok");
			db.execSQL(sql
					+ "(6,1, 5.9 , 7.8 , 9.8 , 62.4 , 67.8 , 73.2 ,1293811200)");
			Log.v("babynote", "insert into hw 6,1 ok");
			db.execSQL(sql
					+ "(7,1, 6.4 , 8.3 , 10.3 , 64.1 , 69.5 , 74.8 ,1293811200)");
			Log.v("babynote", "insert into hw 7,1 ok");
			db.execSQL(sql
					+ "(8,1, 6.9 , 8.8 , 10.8 , 65.7 , 71.0 , 76.3 ,1293811200)");
			db.execSQL(sql
					+ "(9,1, 7.2 , 9.2 , 11.3 , 67.0 , 72.3 , 77.6 ,1293811200)");
			db.execSQL(sql
					+ "(10,1, 7.6 , 9.5 , 11.7 , 68.3 , 73.6 , 78.9 ,1293811200)");
			db.execSQL(sql
					+ "(11,1, 7.9 , 9.9 , 12.0 , 69.6 , 74.9 , 80.2 ,1293811200)");
			db.execSQL(sql
					+ "(0,0, 2.2 , 3.2 , 4.0 , 45.5 , 49.9 , 54.2 ,1293811200)");
			Log.v("babynote", "insert into hw 0,0 ok");
			db.execSQL(sql
					+ "(1,0, 2.8 , 4.0 , 5.1 , 49.0 , 53.5 , 58.1 ,1293811200)");
			Log.v("babynote", "insert into hw 0,0 ok");
			db.execSQL(sql
					+ "(2,0, 3.3 , 4.7 , 6.1 , 52.0 , 56.8 , 61.6 ,1293811200)");
			db.execSQL(sql
					+ "(3,0, 3.9 , 5.4 , 7.0 , 54.6 , 59.5 , 64.5 ,1293811200)");
			db.execSQL(sql
					+ "(4,0, 4.5 , 6.0 , 7.7 , 56.9 , 62.0 , 67.0 ,1293811200)");
			db.execSQL(sql
					+ "(5,0, 5.0 , 6.7 , 8.4 , 58.9 , 64.1 , 69.3 ,1293811200)");
			db.execSQL(sql
					+ "(6,0, 5.5 , 7.2 , 9.0 , 60.6 , 65.9 , 71.2 ,1293811200)");
			db.execSQL(sql
					+ "(7,0, 5.9 , 7.7 , 9.6 , 62.2 , 67.6 , 72.9 ,1293811200)");
			db.execSQL(sql
					+ "(8,0, 6.3 , 8.2 , 10.1 , 63.7 , 69.1 , 74.5 ,1293811200)");
			db.execSQL(sql
					+ "(9,0, 6.6 , 8.6 , 10.5 , 65.0 , 70.4 , 75.9 ,1293811200)");
			db.execSQL(sql
					+ "(10,0, 6.9 , 8.9 , 10.9 , 66.2 , 71.8 , 77.3 ,1293811200)");
			db.execSQL(sql
					+ "(11,0, 7.2 , 9.2 , 11.3 , 67.5 , 73.1 , 78.7 ,1293811200)");
			Log.v("babynote", "insert into hw ok");
		} catch (Exception e) {
			Log.v("babynote", "init hw e=" + e.getMessage());
		}

	}

	public void InitFood() {
		String sql = "INSERT INTO foods (food_id, food_prop_id,foodname,unit ,category,ts) VALUES";

		try {
			db.execSQL(sql + "(1,1,'母乳','毫升','母乳',1293811200)");
			db.execSQL(sql + "(2,2,'雀巢','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(3,2,'惠氏','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(4,2,'贝因美','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(5,2,'雅培','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(6,2,'多美滋','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(7,2,'伊利','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(8,2,'完达山','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(9,2,'圣元','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(10,2,'美赞臣','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(11,2,'雅士利','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(12,2,'其他奶粉','毫升','奶粉',1293811200)");
			db.execSQL(sql + "(13,3,'水果蔬菜泥','克','辅食',1293811200)");
			db.execSQL(sql + "(14,4,'米粉奶糊面粥泥','克','辅食',1293811200)");
			db.execSQL(sql + "(15,5,'蛋黄泥','克','辅食',1293811200)");
			db.execSQL(sql + "(16,6,'豆腐泥','克','辅食',1293811200)");
			db.execSQL(sql + "(17,7,'藕粉','克','辅食',1293811200)");
			db.execSQL(sql + "(18,8,'肉泥肝泥','克','辅食',1293811200)");
			db.execSQL(sql + "(19,9,'钙','毫克','营养品',1293811200)");
			db.execSQL(sql + "(20,10,'铁','毫克','营养品',1293811200)");
			db.execSQL(sql + "(21,11,'锌','毫克','营养品',1293811200)");
			db.execSQL(sql + "(22,12,'维生素','毫克','营养品',1293811200)");
			db.execSQL(sql + "(23,13,'DHA','毫克','营养品',1293811200)");
			db.execSQL(sql + "(24,14,'牛初乳益生菌','克','营养品',1293811200)");
			db.execSQL(sql + "(25,15,'蛋白粉','克','营养品',1293811200)");
			db.execSQL(sql + "(26,16,'鱼肝油','毫克','营养品',1293811200)");
			db.execSQL(sql + "(27,17,'水','毫升','水',1293811200)");
			Log.v("babynote", "insert into foods ok");
		} catch (Exception e) {
			Log.v("babynote", "init foods e=" + e.getMessage());
		}

	}

	public static int nowTime() {
		/*
		 * Calendar c = Calendar.getInstance(); c.setTimeInMillis(new
		 * Date().getTime()); SimpleDateFormat dateFormat = new
		 * SimpleDateFormat( "yyyy-MM-dd-HH-mm-ss"); return
		 * dateFormat.format(c.getTime());
		 */

		return (int) ((new Date().getTime()) / 1000);
	}

	public boolean AddFeed(int food_id, int user_id, int r_date, int r_time,
			float quantity) {
		Log.v("db", "food_id" + food_id + "user_id" + user_id + "date" + r_date
				+ "time" + r_time + "quant" + quantity);
		String sql = "INSERT INTO feeds(food_id,user_id,r_date,r_time,quantity,ts )"
				+ "VALUES"
				+ "("
				+ food_id
				+ ","
				+ user_id
				+ ","
				+ r_date
				+ ","
				+ r_time + "," + quantity + "," + nowTime() + ")";
		try {
			db.execSQL(sql);
			Log.v("babynote", "insert Table feeds ok");
			return true;

		} catch (Exception e) {
			Log.v("babynote", "insert Table feeds err=" + sql);
			Log.v("babynote", e.getMessage());
			return false;
		}
	}

	public boolean AddVisitor(int visitor_id, int time, int birth, int gender) {
		String sql = "INSERT INTO visitors (visitor_id,first_use_time, birth, gender)"
				+ "VALUES"
				+ "("
				+ visitor_id
				+ ","
				+ time
				+ ","
				+ birth
				+ ","
				+ gender + ");";
		try {
			db.execSQL(sql);
			Log.v("babynote", "insert Table visitors ok");
			return true;

		} catch (Exception e) {
			Log.v("babynote", "insert Table visitors err=" + sql);
			Log.v("babynote", e.getMessage());
			return false;
		}
	}

	public boolean AddUser(int visitor_id, int time, int birth, int gender) {
		String sql = "INSERT INTO visitors (visitor_id,first_use_time, birth, gender)"
				+ "VALUES"
				+ "("
				+ visitor_id
				+ ","
				+ time
				+ ","
				+ birth
				+ ","
				+ gender + ");";
		try {
			db.execSQL(sql);
			Log.v("babynote", "insert Table visitors ok");
			return true;

		} catch (Exception e) {
			Log.v("babynote", "insert Table visitors err=" + sql);
			Log.v("babynote", e.getMessage());
			return false;
		}
	}

	public boolean AddHWUser(int user_id, float height, float weight) {
		String sql = "INSERT INTO hwuser (user_id,height, weight,ts)"
				+ "VALUES" + "(" + user_id + "," + height + "," + weight + ","
				+ nowTime() + ")";
		try {
			db.execSQL(sql);
			Log.v("babynote", "insert Table hwuser ok");
			return true;

		} catch (Exception e) {
			Log.v("babynote", "insert Table hwuser err=" + sql);
			Log.v("babynote", e.getMessage());
			return false;
		}

	}

	public boolean AddWeibo(int user_id, String weibo_account,
			String weibo_passwd) {
		String sql = "INSERT INTO weibo(user_id,weibo_account, weibo_passwd)"
				+ "VALUES" + "(" + user_id + ",'" + weibo_account + "','"
				+ weibo_passwd + "');";
		try {
			db.execSQL(sql);
			Log.v("babynote", "insert Table weibo ok");
			return true;

		} catch (Exception e) {
			Log.v("babynote", "insert Table weibo err=" + sql);
			return false;
		}

	}

	public boolean AddFeedback(int user_id, int time, String content) {

		String sql = "INSERT INTO feedbacks (user_id,r_time,content )"
				+ "VALUES" + "(" + user_id + "," + time + ",'" + content
				+ "');";
		try {
			db.execSQL(sql);
			Log.v("babynote", "insert Table feedbacks ok");
			return true;

		} catch (Exception e) {
			Log.v("babynote", "insert Table feedbacks err=" + sql);
			Log.v("babynote", e.getMessage());
			return false;
		}
	}

	public void updateUserInfo(int birth, int gender, int id) {
		db.execSQL("update users set birth=" + birth + ", gender=" + gender
				+ ", ts=" + NotedbHelper.nowTime());
		Log.v("babynote", "update userinfo birth=" + birth + " gender="
				+ gender + " user_id=" + id);
	}

	public void updateVisitorInfo(int birth, int gender, int id) {
		db.execSQL("update visitors set birth=" + birth + ", gender=" + gender
				+ " where visitor_id=" + id);
		Log.v("babynote", "update visitorinfo birth=" + birth + " gender="
				+ gender + "visitor_id=" + id);
	}

	public void FirstStart() {
		try {
			String col[] = { "type", "name" };
			Cursor c = db.query("sqlite_master", col, "name='visitors'", null,
					null, null, null);
			int n = c.getCount();
			if (c.getCount() == 0) {
				CreateTable_visitors();
				CreateTable_users();
				CreateTable_foods();
				CreateTable_foodprop();
				CreateTable_tips();
				CreateTable_feeds();
				CreateTable_weibo();
				CreateTable_localavg();
				CreateTable_usages();
				CreateTable_feedback();
				CreateTable_hw();
				CreateTable_ourcontact();
				CreateTable_loadinfo();
				CreateTable_hwuser();
				InitFoodProp();
				InitFood();
				InitHW();
			}

			c.close();
			// test();
			Log.v("babynote", "c.getCount=" + n + "");

		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
	}

	public Cursor getFoodbyProp(int min_id, int max_id) throws SQLException {
		Log.v("babynote", "run get FoodbyPRops cursor food_prop_id >" + min_id
				+ "< " + max_id);
		return db.query("foods", new String[] { "_id", "foodname" },
				"food_prop_id > " + min_id + " and food_prop_id <" + max_id,
				null, null, null, null);

	}

	public Cursor getFoodybyCategory(String category) throws SQLException {
		Log.v("babynote", "run get Food  by category cursor ");
		Cursor cur = db.query("foods", new String[] { "_id", "food_id",
				"foodname", "unit" }, "category" + "='" + category + "'", null,
				null, null, null);
		if (cur != null) {
			cur.moveToFirst();
		}
		return cur;

	}

	public Cursor getFoodbyProp(int prop_id) throws SQLException {

		Log.v("babynote", "run get FoodbyPRop cursor food_prop_id=" + prop_id);
		return db.query("foods", new String[] { "_id", "foodname" },
				"food_prop_id=" + prop_id, null, null, null, null);
	}

	public Cursor getFeeds(int date) throws SQLException {
		Log.v("babynote", "run get feeds cursor date=" + date);
		Cursor cur = null;
		try {
			cur = db.query("feeds a , foods b ", new String[] { "a._id _id",
					"a.r_time  time", "b.foodname  name",
					"a.quantity quantity", "b.unit unit" },
					"a.food_id=b.food_id and a.r_date =" + date, null, null,
					null, null);
			if (cur != null) {
				Log.v("babynote", "feed record" + cur.getCount());
				cur.moveToFirst();
			}

		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		return cur;
	}

	public Cursor getFeedsbyCategory(String cat) {
		Log.v("babynote", "run get feeds by prop cursor");
		Cursor cur = null;
		try {
			cur = db.query("feeds a , foods b ",
					new String[] { "a._id _id", "a.food_id food_id",
							"a.r_time  time", "b.foodname  name", },
					"a.food_id=b.food_id and b.category='" + cat + "'", null,
					null, null, null);
			if (cur != null) {
				Log.v("getfeed", "count" + cur.getCount());
				// cur.moveToFirst();
				cur.moveToLast();
			}

		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		return cur;
	}

	public Cursor getOurContact() {
		Log.v("babynote", "run get our contact");
		Cursor cur = null;
		try {
			cur = db.query("ourcontact",
					new String[] { "_id", "name", "value" }, null, null, null,
					null, null);
			if (cur != null) {
				cur.moveToFirst();
			}
		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		return cur;

	}

	public Cursor getAverage() {
		Log.v("babynote", "run get Average");
		Cursor cur = null;
		try {

			cur = db.query("local_avg", new String[] { "_id", "food_prop",
					"yours", "avgs", "diffs" }, null, null, null, null, null);
			if (cur != null) {
				cur.moveToFirst();
			}
		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		return cur;
	}

	public int getFoodPropid(String food_prop) {
		Log.v("babynote", "run get foodprop cursor ");
		int ret = -1;
		try {
			Cursor cur = db.query("food_prop", new String[] { "food_prop_id" },
					"food_prop='" + food_prop + "'", null, null, null, null);

			if (cur.getCount() > 0) {
				ret = cur.getInt(0);
				cur.close();
			}
		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}

		return ret;
	}

	public Cursor getWeibo() {
		Log.v("babynote", "get Weibo cursor");
		Cursor cur = null;
		try {
			cur = db.query("weibo", new String[] { "user_id","weibo_account" }, null, null,
					null, null, null);
			if (cur != null) {
				cur.moveToFirst();
			}

		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		return cur;

	}

	public Cursor getTips() {

		Log.v("babynote", "get tips cursor");
		Cursor cur = null;
		try {
			cur = db.query("tips", new String[] { "_id", "content", }, null,
					null, null, null, null);
			if (cur != null) {
				cur.moveToFirst();
			}

		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		return cur;
	}

	public Cursor getLoadInfo() {

		Log.v("babynote", "get load info cursor");
		Cursor cur = null;
		try {
			cur = db.query("loadinfo", new String[] { "_id", "content", },
					null, null, null, null, null);
			if (cur != null) {
				cur.moveToFirst();
			}

		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		return cur;
	}

	public Cursor getUser() {
		Log.v("babynote", "run get User cursor ");
		Cursor cur = null;
		try {
			cur = db.query("users a ", new String[] { "a.user_id user_id",
					"a.email  email", "a.birth  birth", "a.gender gender" },
					null, null, null, null, null);
			if (cur != null) {
				cur.moveToFirst();
			}

		} catch (Exception e) {

			Log.v("babynote", "e=" + e.getMessage());
		}
		return cur;
	}

	public int getUserGender() {

		Log.v("babynote ", "get user/visitor gender");
		int ret = -1;
		Cursor cur = null;
		try {
			cur = getUser();
			if (cur != null && cur.getCount() > 0) {
				if (!cur.isNull(0) && !cur.isNull(3)) {
					if (cur.getInt(0) != -1) {
						ret = cur.getInt(3);
					}
				}
				cur.close();
			} else {
				cur = getVisitor();
				if (cur != null && !cur.isNull(2)) {
					ret = cur.getInt(2);
				}
				cur.close();
			}
		} catch (Exception e) {
			cur.close();
			Log.v("babynote", "e=" + e.getMessage());
		}
		cur.close();
		return ret;
	}

	public int getUserAge() {

		Log.v("babynote ", "get user/visitor age");
		Cursor cur = null;
		int age = -1;
		try {
			cur = getUser();
			if (cur != null && cur.getCount() > 0 && !cur.isNull(2)) {
				age = (nowTime() - cur.getInt(2)) / 3600 / 24 / 30;
				cur.close();
			} else {
				Log.v("babynote", "get visitor");
				cur = getVisitor();
				if (cur != null && cur.getCount() > 0) {
					Log.v("babynote", "visitor not null");
					if (!cur.isNull(1)) {
						age = (nowTime() - cur.getInt(1)) / 3600 / 24 / 30;
						Log.v("babynote", " visitor age" + age);
					}
				}
				cur.close();
			}

		} catch (Exception e) {
			cur.close();
			Log.v("babynote", "e=" + e.getMessage());
		}
		Log.v("babynote", "age" + age);
		cur.close();
		return age;
	}

	public int getUserid() {
		Log.v("babynote", "run get User cursor ");
		Cursor cur = null;
		int ret = -1;
		try {
			cur = getUser();

			if (cur != null && cur.getCount() > 0) {
				ret = cur.getInt(0);

			}
		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		cur.close();
		return ret;

	}

	public Cursor getFood(String foodname) throws SQLException {
		Log.v("babynote", "run get Food  name cursor ");
		Cursor cur = db.query("foods", new String[] { "food_id", "foodname",
				"unit" }, "foodname" + "='" + foodname + "'", null, null, null,
				null);
		if (cur != null) {
			cur.moveToFirst();
		}
		return cur;
	}

	public Cursor getFood(int food_id) throws SQLException {
		Log.v("babynote", "run get Food id cursor ");
		Cursor cur = db.query("foods", new String[] { "food_id", "foodname",
				"unit" }, "food_id =" + food_id, null, null, null, null);
		if (cur != null) {
			cur.moveToFirst();
		}
		return cur;
	}

	public Cursor getVisitor() {
		Log.v("babynote", "run get Visitor cursor ");
		Cursor cur = db.query("visitors a ", new String[] { "a.visitor_id id",
				"a.birth  birth", "a.gender gender",
				"a.first_use_time first_use_time" }, null, null, null, null,
				null);
		if (cur != null) {
			cur.moveToFirst();
		}
		return cur;
	}

	public int getVisitorid() {
		Log.v("babynote", "run get Visitor cursor ");
		Cursor cur = null;
		int ret = -1;
		try {
			cur = getVisitor();

			if (cur != null && cur.getCount() > 0) {
				ret = cur.getInt(0);
			}
		} catch (Exception e) {
			Log.v("babynote", "e=" + e.getMessage());
		}
		cur.close();
		return ret;
	}

	public int getFoodid(String foodname) {
		Log.v("babynote", "run get foodid cursor ");
		Cursor cur = getFood(foodname);
		int ret = -1;
		if (cur != null && cur.getCount() > 0) {
			ret = cur.getInt(0);
		}
		cur.close();
		return ret;
	}

	public Cursor getWeight(int age, int gender) throws SQLException {
		Log.v("babynote", "run get weight cursor age=" + age + "gender="
				+ gender);
		Cursor cur = db.query("hw", new String[] { "_id", "min_w min",
				"mid_w mid", "max_w max" }, "age=" + age + " and gender="
				+ gender, null, null, null, null);
		Log.v("babynote", "weight record" + cur.getCount());
		if (cur != null) {
			cur.moveToFirst();

		}
		int col = cur.getColumnIndex("min");
		Log.v("db", "min w" + cur.getFloat(col));
		return cur;
	}

	public Cursor getHeight(int age, int gender) throws SQLException {
		Log.v("babynote", "run get height cursor age=" + age + " gender="
				+ gender);
		Cursor cur = db.query("hw", new String[] { "_id", "min_h min",
				"mid_h mid", "max_h max" }, "age=" + age + " and gender="
				+ gender, null, null, null, null);
		Log.v("babynote", "hw record" + cur.getCount());
		if (cur != null) {
			cur.moveToFirst();
		}
		// int col = cur.getColumnIndex("min");
		// Log.v("db", "min w" + cur.getFloat(col));
		// Log.v("babynote", "cur" + cur.getFloat(1));
		// Log.v("babynote", "curmax" + cur.getFloat(3));
		return cur;
	}

	public Cursor getSumFeeds(int date) throws SQLException {
		Log.v("babynote", "run get sum feeds cursor date=" + date);
		Cursor cur = null;
		cur = db.query("feeds a, foods b", new String[] { "b.foodname name",
				"sum(a.quantity)  sum", "b.unit unit" },
				"a.food_id=b.food_id and a.r_date=" + date, null, "a.food_id",
				null, null);
		Log.v("db", "record" + cur.getCount());
		if (cur != null) {
			Log.v("babynote", "run  into get sum cursor");
			cur.moveToFirst();
		}
		/*
		 * String s = "当日摄入:"; while (!cur.isAfterLast()) { Log.v("notedb",
		 * "get sum info"); s = s + cur.getString(0) + "  " + cur.getFloat(1) +
		 * cur.getString(2) + "\n"; cur.moveToNext(); } cur.close(); return s; }
		 */
		return cur;
	}

	public void close() {
		db.close();
	}

}
